package demo;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.OracleDataSource;


public class DemoJDBC {
	public static void main(String args[]) throws SQLException {
		OracleDataSource ods = new OracleDataSource();
		ods.setURL("jdbc:oracle:thin:@192.168.7.250:1521:XE");
		Connection conn = ods.getConnection("dv", "dv");
		
		// Create Oracle DatabaseMetaData object
		DatabaseMetaData meta = conn.getMetaData();

		// gets driver info:
		System.out.println("JDBC driver version is " + meta.getDriverVersion());
		
		String sql = "select TITLE from books where BOOK_ID = ?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, "A1111");
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				System.out.println(rs.getString(1));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally{
			conn.close();
		}
		
		ods.setURL("jdbc:oracle:thin:dv/dv@192.168.7.250:1521:XE");
//		conn = ods.getConnection();
//		PreparedStatement ps = null;
//		sql = "update books set author_last_name = 'zxc' where book_id = ?";
//		try {
//			ps = conn.prepareStatement(sql);
//			ps.setString(1, "A1111");
//			int i = ps.executeUpdate();
//			System.out.println("update thanh cong" + i);
//		} catch (Exception e) {
//			e.printStackTrace();
//		}
//		finally{
//			ps.close();
//			conn.close();
//		}
		
		
		conn = ods.getConnection();
		CallableStatement cs = null;
		ResultSet rs = null;
		sql = "{call getlistname(?)}";
		try {
			cs = conn.prepareCall(sql);
			cs.registerOutParameter(1, OracleTypes.CURSOR);
//			cs.registerOutParameter(1, Types.VARCHAR);
//			cs.registerOutParameter(2, Types.VARCHAR);
//			cs.registerOutParameter(3, Types.INTEGER);
//			if (cs.execute()) {
			cs.execute();
//			System.out.println("name: " + cs.getString(1));
//			System.out.println("author: " + cs.getString(2));
//			System.out.println("rate: " + cs.getString(3));
//			}
			rs = (ResultSet)cs.getObject(1);
			while (rs.next()) {
				String name = rs.getString(1);
				String author = rs.getString(2);
				BigDecimal rate = rs.getBigDecimal(3);
				System.out.print("name: " + name);
				System.out.print("; author: " + author);
				System.out.println("; rate : " + rate);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			cs.close();
			conn.close();
		}
		
		
	}
}
